package reports

import (
	"bytes"
	"fmt"
	"strings"
	"time"

	"shqsoft.cn/pas3ui/base"
)

func createDataRangeKhs(repConf *Config, tName string, paramVals map[string]interface{}) (err error) {
	if repConf.DataSource.DFTable == "" {
		err = fmt.Errorf("生成报表%s的Khs出错，DataSource.DFTable不能为空", repConf.Name)
		return
	}
	if repConf.DataSource.TmpTableName == "" {
		err = fmt.Errorf("生成报表%s的Khs出错，DataSource.TmpTableName不能为空", repConf.Name)
		return
	}
	var dbHelp *DataBase
	dbHelp, err = NewDataBase(tName, repConf)
	if err != nil {
		return
	}
	var exists bool
	exists, err = dbHelp.tableTxists()
	if err != nil {
		return
	}
	if exists {
		return
	}
	reptLock.Lock(tName)
	defer reptLock.Unlock(tName)
	var date time.Time
	if paramVals["date"] != nil {
		date = paramVals["date"].(time.Time)
	}
	if date.IsZero() {
		err = fmt.Errorf("生成报表%s出错，参数date不能为空", repConf.Name)
		return
	}
	curDate := date.Format("2006-01-02")
	snmdate := time.Date(date.Year(), 1, 1, 0, 0, 0, 0, time.Local).AddDate(0, 0, -1).Format("2006-01-02")
	var strSql string
	strSql = "CREATE TABLE IF NOT EXISTS TMP_INDI_CLIE_DIVI(CUID UInt64,DPIDS Array(UInt32),EMIDS Array(UInt32),DSVLS Array(Float64)) ENGINE = Join(ANY,LEFT,CUID)"
	base.CHDB.Exec(strSql)
	err = base.CHDB.Exec(strSql).Error
	if err != nil {
		return
	}
	err = base.CHDB.Exec("TRUNCATE TABLE TMP_INDI_CLIE_DIVI").Error
	if err != nil {
		return
	}
	strSql = "CREATE TABLE IF NOT EXISTS TMP_INDI_CLIE_DIVI2(CUID UInt64,DPIDS Array(UInt32),EMIDS Array(UInt32),DSVLS Array(Float64)) ENGINE = Join(ANY,LEFT,CUID)"
	base.CHDB.Exec(strSql)
	err = base.CHDB.Exec(strSql).Error
	if err != nil {
		return
	}
	err = base.CHDB.Exec("TRUNCATE TABLE TMP_INDI_CLIE_DIVI2").Error
	if err != nil {
		return
	}
	strSql = fmt.Sprintf(`INSERT INTO TMP_INDI_CLIE_DIVI(CUID,DPIDS,EMIDS,DSVLS)
	SELECT A.CUID,groupArray(B.DPID) DPIDS,groupArray(B.EMID) EMIDS,groupArray(B.ACBL/A.ACBL) DSVLS FROM
	(SELECT CUID,SUM(ACBL) ACBL FROM
	(SELECT CUID,SUM(ACBL1) ACBL FROM pas_olap_nj.DF_INDI_ACCO_ACBL WHERE DTID='%s' AND ACBL1>0 GROUP BY CUID
	UNION ALL
	SELECT CUID,SUM(ACBL1) ACBL FROM pas_olap_nj.DF_INDI_FANA_ACBL WHERE DTID='%s' AND ACBL1>0 GROUP BY CUID)
	GROUP BY CUID) A
	INNER JOIN 
	(SELECT CUID,DPID,EMID,SUM(ACBL) ACBL FROM
	(SELECT CUID,DPID,EMID,SUM(ACBL1) ACBL FROM pas_olap_nj.DF_INDI_ACCO_ACBL WHERE DTID='%s' AND ACBL1>0 GROUP BY CUID,DPID,EMID
	UNION ALL
	SELECT CUID,DPID,EMID,SUM(ACBL1) ACBL FROM pas_olap_nj.DF_INDI_FANA_ACBL WHERE DTID='%s' AND ACBL1>0 GROUP BY CUID,DPID,EMID)
	GROUP BY CUID,DPID,EMID
	) B ON A.CUID=B.CUID
	GROUP BY A.CUID`, curDate, curDate, curDate, curDate)
	err = base.CHDB.Exec(strSql).Error
	if err != nil {
		return
	}
	strSql = `INSERT INTO TMP_INDI_CLIE_DIVI2(CUID,DPIDS,EMIDS,DSVLS)
	SELECT * FROM TMP_INDI_CLIE_DIVI`
	err = base.CHDB.Exec(strSql).Error
	if err != nil {
		return
	}
	strSql = fmt.Sprintf(`INSERT INTO TMP_INDI_CLIE_DIVI(CUID,DPIDS,EMIDS,DSVLS)
	SELECT B.CUID,DIVI.DPID DPIDS,DIVI.EMID EMIDS,DIVI.DSVL DSVLS
	FROM T_INDI_ACCO_DIVI_ZIPPER A INNER JOIN 
	(SELECT CUID,MAX(ID) ACID FROM T_INDI_ACCO WHERE CUID NOT IN (SELECT CUID FROM TMP_INDI_CLIE_DIVI2) GROUP BY CUID) B ON A.ACID=B.ACID
	WHERE toDate('%s')>=A.SDATE AND toDate('%s')<A.EDATE`, curDate, curDate)
	err = base.CHDB.Exec(strSql).Error
	if err != nil {
		return
	}
	groupBy := strings.Join(repConf.KeyColumns, ",")
	var buff bytes.Buffer
	buff.WriteString("INSERT INTO ")
	buff.WriteString(tName)
	buff.WriteString("(")
	for _, col := range repConf.Columns {
		buff.WriteString(col.Name)
		buff.WriteString(",")
	}
	buff.Truncate(buff.Len() - 1)
	buff.WriteString(")")
	buff.WriteString(" SELECT ")
	for _, col := range repConf.Columns {
		if base.InStringSlice(col.Name, repConf.KeyColumns) {
			colExp := col.ColExp
			if colExp == "" {
				colExp = col.Name
			}
			buff.WriteString(colExp)
			buff.WriteString(" AS ")
			buff.WriteString(col.Name)
		} else if col.Name == "QSHKHS" {
			buff.WriteString(fmt.Sprintf("sumIf(B.DSVL,DTID='%s' AND CHGD='03') AS QSHKHS", snmdate))
		} else if col.Name == "SHKHS" {
			buff.WriteString(fmt.Sprintf("sumIf(B.DSVL,DTID='%s' AND CHGD='03') AS SHKHS", curDate))
		} else if col.Name == "QCFKHS" {
			buff.WriteString(fmt.Sprintf("sumIf(B.DSVL,DTID='%s' AND CHGD='05') AS QCFKHS", snmdate))
		} else if col.Name == "CFKHS" {
			buff.WriteString(fmt.Sprintf("sumIf(B.DSVL,DTID='%s' AND CHGD='05') AS CFKHS", curDate))
		} else if col.Name == "QBJKKHS" {
			buff.WriteString(fmt.Sprintf("sumIf(B.DSVL,DTID='%s' AND CHGD='10') AS QBJKKHS", snmdate))
		} else if col.Name == "BJKKHS" {
			buff.WriteString(fmt.Sprintf("sumIf(B.DSVL,DTID='%s' AND CHGD='10') AS BJKKHS", curDate))
		} else if col.Name == "QJKKHS" {
			buff.WriteString(fmt.Sprintf("sumIf(B.DSVL,DTID='%s' AND CHGD='20') AS QJKKHS", snmdate))
		} else if col.Name == "JKKHS" {
			buff.WriteString(fmt.Sprintf("sumIf(B.DSVL,DTID='%s' AND CHGD='20') AS JKKHS", curDate))
		} else if col.Name == "QYJXKHS" {
			buff.WriteString(fmt.Sprintf("sumIf(B.DSVL,DTID='%s' AND CHGD='30') AS QYJXKHS", snmdate))
		} else if col.Name == "YJXKHS" {
			buff.WriteString(fmt.Sprintf("sumIf(B.DSVL,DTID='%s' AND CHGD='30') AS YJXKHS", curDate))
		} else {
			if col.ColExp == "" {
				err = fmt.Errorf("生成报表%s的Khs出错，列%s没有定义ColExp", repConf.Name, col.Name)
				return
			}
			buff.WriteString(col.ColExp)
			buff.WriteString(" ")
			buff.WriteString(col.Name)
		}
		buff.WriteString(",")
	}
	buff.Truncate(buff.Len() - 1)
	buff.WriteString(" FROM ")
	buff.WriteString(repConf.DataSource.DFTable)
	buff.WriteString(" A LEFT JOIN  (SELECT CUID,EMIDS AS EMID,DSVLS AS DSVL FROM TMP_INDI_CLIE_DIVI ARRAY JOIN EMIDS,DSVLS) B ON A.CUID=B.CUID")
	buff.WriteString(" WHERE DTID IN (?,?) AND CHGD IN('03','05','10','20','30')")
	buff.WriteString(" GROUP BY ")
	buff.WriteString(groupBy)
	buff.WriteString(" HAVING (QSHKHS<>0 OR SHKHS<>0 OR QCFKHS<>0 OR CFKHS<>0 OR QBJKKHS<>0 OR BJKKHS<>0 OR QJKKHS<>0 OR JKKHS<>0 OR QYJXKHS<>0 OR YJXKHS<>0 )")
	buff.WriteString(" ORDER BY STNO DESC ")
	exists, err = dbHelp.createTable("Memory")
	if err != nil {
		return
	}
	if exists {
		return
	}
	err = base.CHDB.Exec(buff.String(), curDate, snmdate).Error
	if err != nil {
		base.CHDB.Exec(fmt.Sprintf("drop table %s", tName))
	}
	return
}
